package com.kaifamiao.month05.day18;

import oracle.jdbc.OracleTypes;
import org.junit.Test;

import java.sql.*;

public class Demo {

    private final String url = "jdbc:oracle:thin:@localhost:1521/xe";
    private final String username = "root";
    private final String password = "root";

    @Test
    public void insert() {
        try (Connection conn = DriverManager.getConnection(url, username, password);
             Statement st = conn.createStatement();) {
            String sql = "insert into users(id,name,pass,birth) values(UID_SEQ.nextval,'孙六','123456',sysdate)";
            int rows = st.executeUpdate(sql, new String[]{"id"});
            if (rows > 0) {
                System.out.println("插入成功！");
                ResultSet set = st.getGeneratedKeys(); // 获取受影响的结果集
                while (set.next()) { // 迭代结果集
                    System.out.println(set.getObject(1));
                }
            } else {
                System.out.println("插入失败！");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void delete() {
        try (Connection conn = DriverManager.getConnection(url, username, password);
             Statement st = conn.createStatement();) {
            String sql = "delete from users where id = 1";
            int rows = st.executeUpdate(sql);
            if (rows > 0) {
                System.out.println("删除成功！");
            } else {
                System.out.println("删除失败！");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void update() {
        try (Connection conn = DriverManager.getConnection(url, username, password);
             Statement st = conn.createStatement();) {
            String sql = "update users set pass = '666666' where name = '张三'";
            int rows = st.executeUpdate(sql);
            if (rows > 0) {
                System.out.println("信息修改成功！");
            } else {
                System.out.println("信息修改失败！");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void selectAll() {
        try (Connection conn = DriverManager.getConnection(url, username, password);
             Statement st = conn.createStatement();) {
            String sql = "select * from users";
            ResultSet set = st.executeQuery(sql); // 获取结果集
            int count = set.getMetaData().getColumnCount(); // 获取结果集个数
            while (set.next()) {
                for (int i = 1; i <= count; i++) {
                    String colName = set.getMetaData().getColumnLabel(i);  // 获取每一列的名称
                    System.out.print(set.getObject(colName) + "\t");
                }
                System.out.println();
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void login() {
        String name = "孙六";
        String pass = "123456";
        String sql = "select * from users where name = ? and pass = ?";
        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement st = conn.prepareStatement(sql);) {
            st.setObject(1, name);
            st.setObject(2, pass);
            ResultSet rs = st.executeQuery();
            int count = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                for (int i = 1; i <= count; i++) {
                    System.out.print(rs.getMetaData().getColumnLabel(i) + "\t");
                }
                System.out.println();
                for (int i = 1; i <= count; i++) {
                    System.out.print(rs.getObject(i) + "\t");
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void procedure() {  // 创建过程
        String sql = "create or replace procedure getTotal(no in number, totalSal out number) " +
                "as " +
                "begin  " +
                " select sal * 12 + nvl(comm,0) into totalSal from emp where empno = no; " +
                "end;";
        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement st = conn.prepareStatement(sql);) {
            st.executeQuery();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void executeProcedure() {
        String sql = "{call getTotal(?, ?)}";
        try (Connection conn = DriverManager.getConnection(url, username, password);
             CallableStatement st = conn.prepareCall(sql);) {
            st.setObject(1, 7839); // in 类型
            st.registerOutParameter(2, OracleTypes.NUMBER); // out 类型只需要传递参数的类型
            st.execute();
            System.out.println(st.getObject(2)); // 获取 out 类型的结果并输出
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void function() {
        String sql = "CREATE OR REPLACE FUNCTION " +
                "getTax(emp_no NUMBER,base_sal NUMBER default 1500) RETURN NUMBER " +
                " IS " +
                "     s NUMBER; " +
                " BEGIN " +
                "    SELECT sal + nvl(comm,0) INTO s FROM emp WHERE empno = emp_no; " +
                "    IF s > base_sal THEN " +
                "        RETURN base_sal*0.2; " +
                "    END IF; " +
                "    RETURN 0; " +
                " END getTax;";
        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement st = conn.prepareStatement(sql);) {
            st.execute();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void executeFunction() {
        String sql = "{? = call getTax (?, ?)}";
        try (Connection conn = DriverManager.getConnection(url, username, password);
             CallableStatement st = conn.prepareCall(sql);) {
            st.registerOutParameter(1, OracleTypes.NUMBER); // out 类型只需要传递参数的类型
            st.setObject(2, 7839); // in 类型
            st.setObject(3, 2000); // in 类型
            st.execute();
            System.out.println("需要纳税：" + st.getObject(1)); // 获取 out 类型的结果并输出
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}
